'use strict';
const titbit = require('titbit'),
      mysql = require('mysql2/promise');

const app = new titbit({
    debug:true,
    https:true,
    key:'/usr/local/cert/5487247_yali.geekwhite.cn.key',     //证书
    cert:'/usr/local/cert/5487247_yali.geekwhite.cn.pem'
});

let con = mysql.createPool({
    host:'localhost',
    user:'root',
    password:'iqjLryKLu9%C',
    database:'project'
});

let week = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
let sno,name;
/*
var express = require('express');
var router = express.Router();
var xlsx = require('node-xlsx');
var fs = require('fs');

router.get('/importExcel', function(req, res, next) {
 var filename='./public/test.xlsx';
 console.error(filename);
var obj = xlsx.parse(filename);
console.log(JSON.stringify(obj));

res.send('import successfully!')
});
router.get('/exportExcel', function(req, res, next) {

var data = [[1,2,3],[true, false, null, 'sheetjs'],['foo','bar',new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
var buffer = xlsx.build([{name: "mySheetName", data: data}]);
fs.writeFileSync('b.xlsx', buffer, 'binary');
res.send('export successfully!');

});
 

var formidable = require('formidable');
var http = require('http');
var util = require('util');
//var express = require('express');
//var fs = require('fs');
var path = require('path');
var favicon = require('serve-favicon');
var bodyParser = require('body-parser');

var apps = express();
var server = http.createServer(apps);

apps.set('views', '/views');
//app.use(favicon('/public/favicon.ico'));
apps.use(bodyParser());
apps.use('/public',express.static(path.join(__dirname, 'public')));


apps.get('/',function(req, res) {

res.writeHead(200, {'content-type': 'text/html'});

app.post('/upload', function(req,res) {

 console.log(" ########## POST /upload ####### ");
    var fileTypeError = false;
    var target_path = __dirname+"/upload";
    var form = new formidable.IncomingForm();
    form.encoding = 'utf-8';
    form.keepExtensions = true;
    form.maxFieldsSize = 10 * 1024 * 1024;
    form.uploadDir = target_path;

    var fields = [];
    var files = [];

    form.on('field', function (field, value) {
        fields.push([field, value]);
    });
    form.on('file', function (field, file) {
        console.log('upload file: ' + file.name);
       //判断文件类型是否是xlsx
        if (file.type != 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
            fileTypeError = true;
        }
        files.push([field, file]);
    });

    form.on('end', function () {

        //遍历上传文件
        var fileName = '';
        var obj = '';
        var folder_exists = fs.existsSync(target_path);
        if (folder_exists) {
            var dirList = fs.readdirSync(target_path);
            dirList.forEach(function (item) {
                if (!fs.statSync(target_path + '/' + item).isDirectory()) {
                    console.log('parse item:' + target_path + '/' + item);
                    fileName = target_path + '/' + item;
                    if (!fileTypeError) {
                        //解析excel
                        obj = xlsx.parse(fileName);
                        console.log(JSON.stringify(obj));
                        //insert into DB
                        //todo
                        res.send({"rtnCode": "0", "rtnInfo": "成功导入数据"});
                    } else {
                        res.send({"rtnCode": "1", "rtnInfo": "文件格式不正确"});
                    }
                    //delete file
                    fs.unlinkSync(fileName);

                }
            });
        }else{
            res.send({"rtnCode": "1", "rtnInfo": "系统错误"});
        }

    });
    form.on('error', function(err) {
        res.send({"rtnCode": "1", "rtnInfo": "上传出错"});
    });
    form.on('aborted', function() {
        res.send({"rtnCode": "1", "rtnInfo": "放弃上传"});
    });
    form.parse(req);


});


*/
//登录  接收前端数据进行判断，信息正确跳转首页/login
app.post('/login',async (c,next)=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-control-Allow-Methods','GET,POST');
    c.body=JSON.parse(c.body);

    let username=c.body.username;
    sno = username;
    const sql = `select * from Student where Sno='${username}'`;
    let [rows1] = await con.execute(sql);
    //console.log(rows1);
    //let sno = JSON.stringify(rows1);
    if(rows1.length===0){
        c.res.body = '该用户名不存在';
}else{
    let pwd1=c.body.password;
    //判断密码是否 正确
    const sql1=`select * from Student where Sno='${username}'`;
    let [rows]=await con.execute(sql1);
    let str=JSON.stringify(rows[0]);
    let pwd=JSON.parse(str).Pwd;
    name = JSON.parse(str).Sname;
    let data = {name:name,bool:true};
    if(pwd===pwd1){
        //c.res.body='true';
        c.res.body = JSON.stringify(data);
}
    // const s=`select * from Student`;
    // let [rowss]=await con.execute(s);
    else{
        c.res.body='密码错误';
    }
}
});
//首页教室显示 返回classroom表/class
/*app.post('/class',async (c,next)=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Origin','GET,POST');
    c.body=JSON.parse(c.body);


    const sql2=`select * from Classroom`;
    let [rows]=await con.execute(sql2);
    let data=JSON.stringify(rows)
    c.res.body=data;

});*/
//教室详情  /date    /week
//获取今天的日期，接收Cno，查询星期表，返回数据
app.post('/date',async (c,next)=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    c.body=JSON.parse(c.body);
    var myDate=new Date();
    var days=myDate.getDay();
    let number=c.body.classroom;
    console.log(c.body);
    let d = week[days];
    console.log(d);
    let sql3 = `select * from Lesson where Cno='${number}' and Day='${d}'`;
    /*switch (days){
      case 1:
         sql3= `select *  from Lesson where Cno='${number}'and Day='Monday'`;
      break;
      case 2:
         sql3= `select * from Lesson where Cno='${number}' and Day='Tuesday'`;
      break;
      case 3:
         sql3=`select *  from Wednesday where Cno='${number}'`;
      break;
      case 4:
         sql3=`select * from Thursday where Cno='${number}'`;
      break;
      case 5:
         sql3=`select * from Friday where Cno='$number}'`;
      break;
      case 6:
         sql3=`select * from Saturday where Cno='${number}'`;
      break;
      case 7:
          sql3=`select * from Sunday where Cno='${number}'`;
      break;
};*/

    let [rows]=await con.execute(sql3);
    let data=JSON.stringify(rows);
    console.log(data);
    c.res.body=data;



});
//接收教室Cno，查询星期表，返回一周课程表
app.post('/week',async (c,next)=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-control-Allow-Methods','GET,POST');
    c.body=JSON.parse(c.body);
    let type = c.body.type;
    console.log(c.body);
    let num=c.body.classroom;
    let id,start,end,cno,coursename,time,types,day;
    if(type==='delf'){
       cno = c.body.classroom;
       start = c.body.Start;
       coursename = c.body.Coursename;
       //let starttime =Number( start.slice(-3,0)+start.slice(3,5));

       const sqls1 = `delete from Lesson where Cno='${cno}' and Coursename='${coursename}' and Start=${start}`;
       let [row1] = await con.execute(sqls1);
       const sqls4 = `delete from Activity where Cno='${cno}' and Aname='${coursename}' and Start='${start}'`;
       let [row4] = await con.execute(sqls4);

    }
    if(type==='adds'){
       id = new Date();
       start = c.body.Start;
       end = c.body.Endtime;
       cno = c.body.classroom;
       coursename = c.body.Coursename;
       day = c.body.Day;
       let starttime =Number(start.slice(0,2)+start.slice(-2));
       let endtime = Number(end.slice(0,2)+end.slice(-2));
       //let starts = start.slice(0,2)+start.slice(3,5);
       //console.log(starts);
       const sqls2 = `insert into Lesson values(null,'${starttime}','${endtime}','${cno}','${coursename}',null,null,'${day}')`;
       let [row2] = await con.execute(sqls2);

       console.log(row2);
     }


    const sql1=`select * from Lesson where Cno='${num}' and Day='Monday'`;
    const sql2=`select * from Lesson where Cno='${num}' and Day='Tuesday'`;
    const sql3=`select * from Lesson where Cno='${num}' and Day='Wednesday'`;
    const sql4=`select * from Lesson where Cno='${num}' and Day='Thursday'`;
    const sql5=`select * from Lesson where Cno='${num}' and Day='Friday'`;
    const sql6=`select * from Lesson where Cno='${num}' and Day='Saturday'`;
    const sql7=`select * from Lesson where Cno='${num}' and Day='Sunday'`;

    let [rows1]=await con.execute(sql1);
    let [rows2]=await con.execute(sql2);
    let [rows3]=await con.execute(sql3);
    let [rows4]=await con.execute(sql4);
    let [rows5]=await con.execute(sql5);
    let [rows6]=await con.execute(sql6);
    let [rows7]=await con.execute(sql7);

    let data1=rows1;
    let data2=rows2;
    let data3=rows3;
    let data4=rows4;
    let data5=rows5;
    let data6=rows6;
    let data7=rows7;
    let course = [data1,data2,data3,data4,data5,data6,data7];

    console.log(data4);
    c.res.body=JSON.stringify(course);

});
//获取时间，得到周几，还有具体时间段，在星期表筛选结束时间在指定开始之前，开始时间在指定结束时间之后；返回筛选出来的教室；/select
app.post('/select',async (c,next) =>{

    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    c.body = JSON.parse(c.body);
    console.log(c.body);
    let time = c.body.date; //时间类型
    let d = new Date(time);
    let mon = d.getDay();
    let w = week[mon];
    let s = c.body.time1;
    let e = c.body.time2;
    console.log(time);
    let start = s.split(':')[0]+s.split(':')[1];
    let end = e.split(':')[0]+e.split(':')[1];
    //console.log(start,end);

    const sql = `select Cno from Lesson where Day='${w}' and Endtime<${start} union select Cno from Lesson where Day='${w}' and Start>${end}`;
    const [rows] = await con.execute(sql);
    console.log(JSON.stringify(rows));
    c.res.body = JSON.stringify(rows);
/*
    const sqls1 = `select Start from Lesson where Day='${w}'`;
    const sqls2 = `select Endtime from Lesson where Day='${w}'`;
    const [row1] = await con.execute(sqls1);
    const [row2] = await con.execute(sqls2);
    // console.log(row1,row2);
    let starts = JSON.stringify(row1);
    starts = JSON.parse(starts);
    let ends = JSON.stringify(row2);
    //console.log(starts);
    ends = JSON.parse(ends);
    if(starts.length==0){
      const sql = `select Cno from Lesson where Day='${w}'`;
      const [rows] = await con.execute(sql);
      //console.log(JSON.stringify(rows));
      let data = ["全天均可占用",rows];
      c.res.body = JSON.stringify(data);
    }
    for(var i=0;i<starts.length;i++){
      //console.log(starttime,ends[i].Endtime,starts[i].Start);
      if(starttime>=ends[i].Endtime){
        if(i+1>=starts.length || endtime<=starts[i+1].Start){
        //const sql = `select Start from Lesson where Day='${w}' and t='${starts[i+1].Start}'`;
        const sqll = `select Cno from Lesson where Day='${w}' and Endtime='${ends[i].Endtime}'`;
        //const [rows] = await con.execute(sql);
        const [rowss] = await con.execute(sqll);
        //let data = rowss;
        let data = JSON.stringify(rowss);
        console.log(data);
        console.log(123);
        c.res.body=data;
       }
      }
      else if(endtime<=starts[i].Start&&i==0){

        const sql = `select Cno, Start from Lesson where Day='${w}' and Start='${starts[i].Start}'`;
        const [rows] = await con.execute(sql);
        let data = JSON.stringify(rows);
        c.res.body=data;
      }


   }*/
});


//申请页面  返回Student表中的数据；/sinfor
app.post('/sinfor',async c=>{

    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    console.log(c.body);
    c.body=JSON.parse(c.body);
    let type = c.body.type;
    console.log(type);
    let sno,sname,pwd;
   if(type==='delf'){
         sno = c.body.sno
       const sql = `delete from Student where Sno='${sno}'`;
       let [row] = await con.execute(sql);
    }
   if(type==='adds'){
       sno = c.body.sno;
       sname = c.body.sname;
       pwd = c.body.pwd;
       console.log(sno);
       const sql2 = `insert into Student values('${sno}','${pwd}','${sname}')`;
       let [row2] = await con.execute(sql2);
       console.log(row2);
     }
    const sql1 = `select Sno,Sname,Pwd from Student`;
    let [rows] = await con.execute(sql1);
    let sinfor = JSON.stringify(rows);
    console.log(sinfor);
    c.res.body = sinfor;
});
//申请提交  存储信息到Approval表/submit
app.post('/submit',async c=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    let body = JSON.parse(c.body);
    //console.log(123);
    console.log(body);
    let sno = body.number,
        sname = body.name,
        cno = body.classroom,
        start = body.start,
        end = body.end,
        text = body.reason,
        type = body.type,
        aname = body.activetype,
        date = body.dates;
    //let start = start.split('-')[0];
    //let endtime = time.split('-')[1];
    //console.log(time);
    //console.log(123);
    let starttime =Number( start.slice(0,2)+start.slice(3,5));
    let endtime = Number(end.slice(0,2)+end.slice(3,5));

    let d = new Date();
    let now  = d.getTime();
    let d1 = new Date(date);
    let mon = d1.getDay();
    let w = week[mon];
   // console.log(w);

    Date.prototype.toLocaleString = function() {
          return this.getFullYear() + "/" + (this.getMonth() + 1) + "/" + this.getDate() + "/ " + this.getHours() + ":" + this.getMinutes() + ":" + this.getSeconds();
    };
    let submittime = now.toLocaleString();
   //查询同一时间段是否有记录
   const sqls1 = `select Start from Lesson where Cno='${cno}' and Day='${w}'`;
   const sqls2 = `select Endtime from Lesson where Cno='${cno}' and Day='${w}'`;
   const [row1] = await con.execute(sqls1);
   const [row2] = await con.execute(sqls2);
   console.log(row1,row2);
   let starts = JSON.stringify(row1);
   starts = JSON.parse(starts);
   let ends = JSON.stringify(row2);
   console.log(starts);
   ends = JSON.parse(ends);
   //console.log(JSON.parse(starts));
   //console.log(ends);
   if(starts.length==0){
     const sql = `insert into Approval values  ('${sno}','${sname}','${cno}','${starttime}','${endtime}',0,'${submittime}',null,'${text}','${type}','${date}','${aname}')`;
     const [rows] = await con.execute(sql);
     let data = JSON.stringify(rows);
     c.res.body=true;
   }
   for(var i=0;i<starts.length;i++){
      //console.log(starttime,ends[i].Endtime,starts[i].Start);
      if(starttime>=ends[i].Endtime){
         if(i+1>=starts.length || endtime<=starts[i+1].Start){
         const sql = `insert into Approval values('${sno}','${sname}','${cno}','${starttime}','${endtime}',0,'${submittime}',null,'${text}','${type}','${date}','${aname}')`;
       const [rows] = await con.execute(sql);
       let data = JSON.stringify(rows);
       console.log(123);
       c.res.body=true;
         }
   }
   else if(endtime<=starts[i].Start&&i==0){
     console.log(234);
     const sql = `insert into Approval values  ('${sno}','${sname}','${cno}','${starttime}','${endtime}',0,'${submittime}',null,'${text}','${type}','${date}','${aname}')`;
     const [rows] = await con.execute(sql);
     let data = JSON.stringify(rows);
     c.res.body=true;
   }
   else{
    console.log(345);
    c.res.body = false;
   }
  }

});
//审批  显示审批通过和未通过的 /check
app.post('/check',async c=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    //console.log(123);
    c.body = JSON.parse(c.body);
    console.log(c.body);
    let type = c.body.type;
    let Cno = c.body.cno,
        Coursename = c.body.activetype,
        time = c.body.time,
        start = c.body.start,
        end = c.body.end,
        types = c.body.types,
        username=c.body.num,
        text = c.body.text;
    let d = new Date(time);
    let mon = d.getDay();
    let w = week[mon];
    console.log(username);
    //console.log(d);
    //console.log(time);
    start = start+'';
    end = end+'';
    let starttime =Number( start.slice(0,2)+start.slice(3,5));
    let endtime = Number(end.slice(0,2)+end.slice(3,5));
    //console.log(start);
    let Submittime;
    //console.log(type);
    //审核通过  1
    if(type==='ok'){
        Submittime = c.body.Submittime;
        console.log(starttime);
        const sqls1 = `update Approval set lebal=1 where Submittime='${Submittime}'`;
        const [row1] = await con.execute(sqls1);
        const sqls3 = `insert into Lesson values(null,'${starttime}','${endtime}','${Cno}','${Coursename}','${time}','${types}','${w}')`;
        const [row3] = await con.execute(sqls3);
         const sqls5 = `insert into Activity values('${Coursename}','${Cno}','${starttime}','${endtime}','${text}','${time}')`;
        const [row5] = await con.execute(sqls5);

    }
    if(type==='no'){
        Submittime = c.body.Submittime;
        const sqls2 = `update Approval set lebal=2 where Submittime='${Submittime}'`;
        const [row2] = await con.execute(sqls2);
    }
    if (type==='de'){
       Submittime = c.body.Submittime;
       const sqls4 = `delete from Approval where Submittime='${Submittime}'`;
       const [row4] = await con.execute(sqls4);

}
    //审核通过
    const sql = `select * from Approval where lebal=1 and Sno='${username}'`;
    const [rows] =  await con.execute(sql);
    let pass = rows;

    //未审核
    const sql1 = `select * from Approval where lebal=0 and Sno='${username}'`;
    const [rows1] = await con.execute(sql1);
    let nopass = rows1;

    //审核未通过
    const sql2 = `select * from Approval where lebal=2 and Sno='${username}'`;
    const [rows2] = await con.execute(sql2);
    let nonopass = rows2;
    //console.log(JSON.stringify(nopass));
    c.res.body = JSON.stringify([pass,nopass,nonopass]);
});
//客户删除没有用的审批之后的已通过或未通过
app.post('/checkdel',async c=>{
   c.setHeader('Access-Control-Allow-Origin','*');
   c.setHeader('Access-Control-Allow-Methods','GET,POST');
   let body = JSON.parse(c.body);
   console.log(body);
   let Sno = body.item.Sno,
       Cno = body.item.Cno,
       Submittime = body.item.Submittime,
       Text = body.item.Text,
       Type = body.item.Type,
       Aname = body.item.Aname;
   //console.log(Sno,Details);
   const sql = `delete from Approval  where Sno='${Sno}' and Cno='${Cno}' and Submittime='${Submittime}' and Text='${Text}' and Type='${Type}' and Aname='${Aname}'`;
   const [row] = await con.execute(sql);
   /*const sql1 = `select * from  where Sno='${Sno}'`;
   const [rows1] = await con.execute(sql1);
   c.res.body = JSON.stringify(rows1);*/
});

//后端接口 审批
app.post('/aftercheck',async c=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    //console.log(c.body);
    c.body = JSON.parse(c.body);
    let type = c.body.type;
    let Cno = c.body.cno,
        Coursename = c.body.text,
        time = c.body.time,
        start = c.body.start,
        end = c.body.end,
        types = c.body.types,
        text = c.body.text;
    //console.log(c.body);
    let d = new Date(time);
    let mon = d.getDay();
    let w = week[mon];

    //console.log(d);
    //console.log(time);
    start = start+'';
    end = end+'';
    let starttime =Number( start.slice(0,-2)+start.slice(-2));
    let endtime = Number(end.slice(0,-2)+end.slice(-2));
    //console.log(start);
    let Submittime;
    //console.log(type);
    //审核通过  1
    if(type==='ok'){
        console.log(c.body);
        console.log(starttime);
        Submittime = c.body.Submittime;
        const sqls1 = `update Approval set lebal=1 where Submittime='${Submittime}'`;
        const [row1] = await con.execute(sqls1);
        const sqls3 = `insert into Lesson values(null,'${starttime}','${endtime}','${Cno}','${Coursename}','${time}','${types}','${w}')`;
        const [row3] = await con.execute(sqls3);
        const sqls5 = `insert into Activity values('${Coursename}','${Cno}','${starttime}','${endtime}','${text}','${time}')`;
        const [row5] = await con.execute(sqls5);

    }
    if(type==='no'){
        Submittime = c.body.Submittime;
        const sqls2 = `update Approval set lebal=2 where Submittime='${Submittime}'`;
        const [row2] = await con.execute(sqls2);
    }
    if (type==='de'){
       Submittime = c.body.Submittime;
       const sqls4 = `delete from Approval where Submittime='${Submittime}'`;
       const [row4] = await con.execute(sqls4);
}
    //审核通过
    const sql = `select * from Approval where lebal=1`;
    const [rows] =  await con.execute(sql);
    let pass = rows;

    //未审核
    const sql1 = `select * from Approval where lebal=0`;
    const [rows1] = await con.execute(sql1);
    let nopass = rows1;

    //审核未通过
    const sql2 = `select * from Approval where lebal=2`;
    const [rows2] = await con.execute(sql2);
    let nonopass = rows2;
    //console.log(JSON.stringify(nopass));
    c.res.body = JSON.stringify([pass,nopass,nonopass]);
});

//活动通知  从活动表中获取活动的名称，返回本天的活动，若超过活动时间，活动自动删除/inform
app.post('/inform',async c=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    //c.body = JSON.parse(c.body);
    let d = new Date();
    let y = d.getFullYear();
    let m = d.getMonth()+1;
    let day = d.getDate();
    let h = ''+d.getHours();
    let min = d.getMinutes();
    if(min<10){
        min = '0'+min;
    }
    else{
        min=min+'';
    }
    let t = h+min;
    let time = y+'-'+m+'-'+day;
    console.log(t);
    console.log(time);
    const sql = `delete from Activity where Endtime<${t} and time='${time}'`;
    const [rows] = await con.execute(sql);
    const sql1 = `select * from Activity where time='${time}'`;
    const [rows1] = await con.execute(sql1);
    let data = JSON.stringify(rows1);

    c.res.body = data;
});

//活动详情  活动教室、活动时间、活动简介、活动名称、面向群体 根据名字查询该活动的基本信息/detail
app.post('/detail',async c=>{

    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');
    console.log(c.body);
    c.body = JSON.parse(c.body);

    let num = c.body.classroom;
    let type=c.body.type;
    let Coursename = c.body.Coursename,
        time = c.body.Day,
        start = c.body.Start,
        end = c.body.Endtime,
        text = c.body.details;
    let d = new Date(time);
    let mon = d.getDay();
    let w = week[mon];

    //console.log(d);
    console.log(time);
    start = start+'';
    end = end+'';
    let starttime =Number(start.slice(0,2)+start.slice(3,5));
    let endtime = Number(end.slice(0,2)+end.slice(3,5));
    if(type==='adds'){
        const sqls1 = `insert into Lesson values(null,'${starttime}','${endtime}','${num}','${Coursename}','${time}','活动','${w}')`;
        const [row1] = await con.execute(sqls1);
        const sqls2 = `insert into Activity values('${Coursename}','${num}','${starttime}','${endtime}','${text}','${time}')`;
        const [row2] = await con.execute(sqls2);

    }

     if(type==='delf'){
       let start1 = c.body.Start;
       const sqls3 = `delete from Lesson where Cno='${num}' and Coursename='{Coursename}' and Start=${start1}`;
       let [row3] = await con.execute(sqls3);
       const sqls4 = `delete from Activity where Cno='${num}' and Aname='${Coursename}' and Start='${start}'`;
       let [row4] = await con.execute(sqls4);

    }

    const sql = `select * from Activity where Cno='${num}'`;
    const [rows] = await con.execute(sql);

    c.res.body=JSON.stringify(rows);

});
app.post('/manager',async c=>{
  c.setHeader('Access-Control-Allow-Origin','*');
  c.setHeader('Access-Control-Allow-Methods','GET,POST');


  let req=JSON.parse(c.body);
  let username=req.username;
  let password=req.password;
  console.log(123);
  console.log(c.body);
  console.log(req);

const sql=`select * from Manager where (Mno='${username}' and Pwd='${password}')`;
  let [row]=await con.execute(sql);

  let str=JSON.stringify(row[0]);
  console.log(str);
  if(str!=''){
    c.res.body=str;
  }
  else{
    c.res.body='false';
  }

});

app.post('/changeManager',async c=>{
    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-Control-Allow-Methods','GET,POST');

    let req=JSON.parse(c.body);
    let Mno=req.Mno,
        Pwd=req.Pwd,
        Mname=req.Mname;
    //console.log(c.body);
    const sql=`update Manager set Mname='${Mname}',
           Pwd='${Pwd}'  where Mno='${Mno}'`;
    const [row] =  await con.execute(sql);
    //console.log(row);

});
// 小程序端提交故障报修
app.post('/fix',async c=>{
   c.setHeader('Access-Control-Allow-Origin','*');
   c.setHeader('Access-Control-Allow-Methods','GET,POST');
   let body = JSON.parse(c.body);
   let Sname = body.name,
       Tel = body.tel,
       Device = body.device,
       Num = body.number,
       Details = body.reason,
       Sno = body.id;

   console.log(c.body);
   console.log(body);
   const sql = `insert into Fix values('${Sname}','${Tel}','${Device}',${Num},'${Details}',null,0,'${Sno}') `;
   const [rows] = await con.execute(sql);
   const sql1 = `select * from Fix where Sno='${Sno}'`;
   const [rows1] = await con.execute(sql1);
   c.res.body = JSON.stringify(rows1);
});
app.post('/fixall',async c=>{
   c.setHeader('Access-Control-Allow-Origin','*');
   c.setHeader('Access-Control-Allow-Methods','GET,POST');
   let body = JSON.parse(c.body);
   let sno = body.sno;
   const sql1 = `select * from Fix where Sno='${sno}'`;
   const [rows1] = await con.execute(sql1);
   c.res.body = JSON.stringify(rows1);
});
app.post('/fixdel',async c=>{
   c.setHeader('Access-Control-Allow-Origin','*');
   c.setHeader('Access-Control-Allow-Methods','GET,POST');
   let body = JSON.parse(c.body);
   console.log(body);
   let Sno = body.item.Sno,
       Device = body.item.Device,
       Num = body.item.Num,
       Details = body.item.Details;
   console.log(Sno,Details);
   const sql = `delete from Fix where Sno='${Sno}' and Device='${Device}' and Num='${Num}' and Details='${Details}'`;
   const [row] = await con.execute(sql);
   /*const sql1 = `select * from Fix where Sno='${Sno}'`;
   const [rows1] = await con.execute(sql1);
   c.res.body = JSON.stringify(rows1);*/
});

//后台管理系统未修好
app.post('/fix0',async c=>{
   c.setHeader('Access-Control-Allow-Origin','*');
   c.setHeader('Access-Control-Allow-Methods','GET,POST');
   //console.log(c.body);
   let body = JSON.parse(c.body);
   let Sno = body.Sno,
       type = body.type,
       Device = body.Device,
       Details = body.Details,
       Num = body.Num;
   console.log(body);
   if(body.type=='non'){

     const sql1 = `select * from Fix where lebal=0`;
     const [rows1] = await con.execute(sql1);
     c.res.body = JSON.stringify(rows1);
  }
  else{

    const sql2 = `update Fix set lebal=1 where Sno='${Sno}' and Device='${Device}' and Num='${Num}' and Details='${Details}' `;
    const [rows2] = await con.execute(sql2);
    const sql3 = `select * from Fix where lebal=0`;
    const [rows3] = await con.execute(sql3);
    c.res.body = JSON.stringify(rows3);
  }
});
app.post('/fixed',async c=>{
   c.setHeader('Access-Control-Allow-Origin','*');
   c.setHeader('Access-Control-Allow-Methods','GET,POST');


   const sql1 = `select * from Fix where lebal=1`;
   const [rows1] = await con.execute(sql1);
   c.res.body = JSON.stringify(rows1);
});

//管理员端上传完整课表信息 原课表被删除and新课表被插入到数据库
app.post('/changeallclass',async c=>{
  c.setHeader('Access-Control-Allow-Origin','*');
  c.setHeader('Access-Control-Allow-Methods','GET,POST');
console.log(c.body);

});
app.run(3000);
